package com.yangjun.dao.impl.realtimeLiveAnalysis.realtimeSource;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class AndroidIOSImpl {
	
	 @Autowired
	 @Resource(name = "bikadJdbcTemplate")
	 private JdbcTemplate mJdbcTemplate;
	 
	 
	 //************************************************概况******************************************************
	 /***
	  * 总览数据  
	  * @return
	  */
	 public List<Map<String, Object>> getOverviewData(String today,String yestoday, String contrastday,int platform) {
		 String sql = "select to_char(t.date_day, 'YYYY-MM-DD') as data_date,sum(t.new_client) as new_client,"
				 +"       sum(t.open_client_number) as open_client_number,"
				 +"       nvl(round(sum(t.new_client) / sum(t.client_number), 4), 0) as new_client_rate,"
				 +"       sum(t.open_number) as open_number,"
				 +"       nvl(round(sum(t.sum_visit_length) / sum(t.client_number) / 1000, 0),0) as avg_visitlength "
				 +" from DV_LIVE_APP_SUMMARIZE t"
				 +" where site_id = "+platform+" "
				 +" and t.date_day in(date'" + contrastday + "', date'" + yestoday + "', date'" + today + "') "
				 +" group by date_day "
				 +" order by date_day";
		 
		 NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(mJdbcTemplate);
		 MapSqlParameterSource parameters = new MapSqlParameterSource();
		 List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql, parameters);
		 return rows;
	 }
	 /***
	  * 总览数据  今天
	  * @return
	  */
//	 public List<Map<String, Object>> getOverviewDataToday(int platform) {
//		 String sql = "select to_char(t.date_day, 'YYYY-MM-DD') as dataDate,sum(t.new_client) as new_client,"
//				 +"       sum(t.open_client_number) as open_client_number,"
//				 +"       nvl(round(sum(t.new_client) / sum(t.client_number), 4), 0) as new_client_rate,"
//				 +"       sum(t.open_number) as open_number,"
//				 +"       nvl(round(sum(t.sum_visit_length) / sum(t.client_number) / 1000, 0),0) as avg_visitlength "
//				 +" from DV_LIVE_APP_SUMMARIZE t"
//				 +" where site_id = "+platform+"  "
//				 +" and t.date_day=trunc(sysdate) "
//				 +" group by date_day "
//				 +" order by date_day";
//		 
//		 NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(mJdbcTemplate);
//		 MapSqlParameterSource parameters = new MapSqlParameterSource();
//		 List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql, parameters);
//		 return rows;
//	 }
	 

	 /**
	  * 折线图数据
	  * @param name
	  * @param today
	  * @param contrastday
	  * @return
	  */
	 public Map<String , List<Map<String,Object>>> getOverviewLine(int platform,String today, String contrastday) {
		 
		 Map<String , List<Map<String,Object>>> resultMap = new HashMap<String,  List<Map<String,Object>>>();
		 
		 /**
		  * app启动用户数
		  */
		 
		 String startupsql = "select to_char(t.date_day, 'YYYY-MM-DD') as dataDate, "
				 +"       t.t_hour, "
				 +"       round(sum(t.open_client_number),0) as OPEN_CLIENT_NUMBER "
				 +" from DV_LIVE_APP_SUMMARIZE t "
				 +" where site_id = "+platform+"  "
				 +" and (date_day = date '"+today+"' or date_day = date '"+contrastday+"')"
				 +" group by t.date_day, t.t_hour "
				 +" order by t.date_day, t.t_hour ";
		 
		 
//		 System.out.println(startupsql);
		 NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(mJdbcTemplate);
		 MapSqlParameterSource parameters = new MapSqlParameterSource();
		 List<Map<String,Object>>  startUprows = namedParameterJdbcTemplate.queryForList(startupsql, parameters);
		 resultMap.put("startUprows", startUprows);
		 
		 /**
		  * 新用户
		  */
		 String newclientsql = "select to_char(t.date_day, 'YYYY-MM-DD') as dataDate, "
				 +"       t.t_hour, "
				 +"       round(sum(t.new_client),0) as NEW_CLIENT "
				 +" from DV_LIVE_APP_SUMMARIZE t "
				 +" where site_id = "+platform+"  "
				 +" and (date_day = date '"+today+"' or date_day = date '"+contrastday+"')"
				 +" group by t.date_day, t.t_hour "
				 +" order by t.date_day, t.t_hour ";
		 
		 List<Map<String,Object>>  newClientrows = namedParameterJdbcTemplate.queryForList(newclientsql, parameters);
		 resultMap.put("newClientrows", newClientrows);
		 
		 /**
		  * 老用户
		  */
		 String oldclientsql = "select to_char(t.date_day, 'YYYY-MM-DD') as dataDate, "
				 +"       t.t_hour, "
				 +"       round(sum(t.client_number-t.new_client),0) as OLD_CLIENT "
				 +" from DV_LIVE_APP_SUMMARIZE t "
				 +" where site_id = "+platform+"  "
				 +" and (date_day = date '"+today+"' or date_day = date '"+contrastday+"')"
				 +" group by t.date_day, t.t_hour "
				 +" order by t.date_day, t.t_hour ";
		 List<Map<String,Object>>  oldClientrows = namedParameterJdbcTemplate.queryForList(oldclientsql, parameters);
		 resultMap.put("oldClientrows", oldClientrows);
		 
		 /**
		  * 新用户占比
		  */
		 String newclientRatesql = "select to_char(t.date_day, 'YYYY-MM-DD') as dataDate, "
				 +"       t.t_hour, "
				 +"       nvl(round(sum(t.new_client)/sum(t.client_number),4), 0) as NEW_CLIENT_RATE "
				 +" from DV_LIVE_APP_SUMMARIZE t "
				 +" where site_id = "+platform+"  "
				 +" and (date_day = date '"+today+"' or date_day = date '"+contrastday+"')"
				 +" group by t.date_day, t.t_hour "
				 +" order by t.date_day, t.t_hour ";
		 
		 List<Map<String,Object>>  newclientRaterows = namedParameterJdbcTemplate.queryForList(newclientRatesql, parameters);
		 resultMap.put("newclientRaterows", newclientRaterows);	
	    return resultMap;
	}
	 
	 
	 //************************************************新增用户******************************************************
	 
	 /**
	  * 新增用户 应用版本
	  * @return
	  */
	 public List<Map<String,Object>> getNewClientVersion(int platform) {
		 
		 String sql = "select t.versionname, "
				 +"       sum(t.new_client) as new_client, "
				 +"       nvl(round(sum(t.new_client) / max(t.total_new_client), 4), 0) as new_client_rate "
				 +"from ( "
				 +"               select date_day, "
				 +"               versionname, "
				 +"               new_client, "
				 +"               sum(new_client) OVER(PARTITION BY date_day) AS total_new_client "
				 +"               from DV_LIVE_APP_SUMMARIZE "
				 +"               where site_id = "+platform+"  "
				 +"               and date_day = trunc(sysdate) "
				 +"               and new_client > 0 "
				 +"               and versionname is not null "
				 +"  ) t "
				 +" group by t.versionname "
				 +" order by t.versionname ";
		 
		 NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(mJdbcTemplate);
		 MapSqlParameterSource parameters = new MapSqlParameterSource();
		 List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql, parameters);
		 return rows;
		 
	 }
	 /**
	  * 新增用户 渠道
	  * @return
	  */
	 public List<Map<String,Object>> getNewClientUtmSource(int platform) {
		 
		 String sql = "select t.utm_source, "
				 +"       sum(t.new_client) as new_client, "
				 +"       nvl(round(sum(t.new_client) / max(t.total_new_client), 4), 0) as new_client_rate "
				 +"from ( "
				 +"               select date_day, "
				 +"               utm_source, "
				 +"               new_client, "
				 +"               sum(new_client) OVER(PARTITION BY date_day) AS total_new_client "
				 +"               from DV_LIVE_APP_SUMMARIZE "
				 +"               where site_id = "+platform+"  "
				 +"               and date_day = trunc(sysdate) "
				 +"               and new_client > 0 "
				 +"               and utm_source is not null "
				 +"  ) t "
				 +" group by t.utm_source "
				 +" order by t.utm_source ";
		 
		 NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(mJdbcTemplate);
		 MapSqlParameterSource parameters = new MapSqlParameterSource();
		 List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql, parameters);
		 return rows;
		 
	 }
	 
	 /**
	  * 新增 渠道安装量 
	  * @param utmSource
	  * @return
	  */
	 public List<Map<String,Object>> getUtmSourceInstall(int platform,String today, String contrastday) {
		 
		 String sql = "select to_char(t.date_day, 'YYYY-MM-DD') as dataDate, "
				 +"       t.t_hour, "
				 +"       sum(t.new_client) as install_number "
				 +" from DV_LIVE_APP_SUMMARIZE t "
				 +" where site_id = "+platform+"  "
				 +" and (date_day = date '"+today+"' or date_day = date '"+contrastday+"') "
				 +" group by t.date_day, t.t_hour "
				 +" order by t.date_day, t.t_hour" ;
		 
		 NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(mJdbcTemplate);
		 MapSqlParameterSource parameters = new MapSqlParameterSource();
		 List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql, parameters);
		 return rows;
		 
	 }
	 /**
	  * 新增 渠道安装量  (点击渠道时触发)
	  * @param utmSource
	  * @return
	  */
	 public List<Map<String,Object>> getUtmSourceInstallClick(int platform,String utmSource,String today, String contrastday) {
		 
		 String sql = "select to_char(t.date_day, 'YYYY-MM-DD') as dataDate, "
				 +"       t.t_hour, "
				 +"       sum(t.new_client) as install_number "
				 +" from DV_LIVE_APP_SUMMARIZE t "
				 +" where site_id = "+platform+"  "
				 +" and (date_day = date '"+today+"' or date_day = date '"+contrastday+"') "
				 +" and utm_source = '"+utmSource+"' "
				 +" group by t.date_day, t.t_hour "
				 +" order by t.date_day, t.t_hour" ;
		 
		 NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(mJdbcTemplate);
		 MapSqlParameterSource parameters = new MapSqlParameterSource();
		 List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql, parameters);
		 return rows;
		 
	 }
	 
	 
	//************************************************当日活跃******************************************************
	 
	 /**
	  * 全部 应用版本
	  * @return
	  */
	 public List<Map<String,Object>> getAppVersion(int platform) {
		 
		 String sql =  "select t.versionname, "
				 +"       sum(t.client_number) as client_number, "
				 +"       nvl(round(sum(t.client_number) / max(t.total_client_number), 4), 0) as client_number_rate "
				 +"from ( "
				 +"               select date_day, "
				 +"               versionname, "
				 +"               client_number, "
				 +"               sum(client_number) OVER(PARTITION BY date_day) AS total_client_number "
				 +"               from DV_LIVE_APP_SUMMARIZE "
				 +"               where site_id = "+platform+"  "
				 +"               and date_day = trunc(sysdate) "
				 +"               and versionname is not null "
				 +"  ) t "
				 +" group by t.versionname "
				 +" order by t.versionname ";
		 
		 NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(mJdbcTemplate);
		 MapSqlParameterSource parameters = new MapSqlParameterSource();
		 List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql, parameters);
		 return rows;
		 
	 }
	 /**
	  * 全部  渠道
	  * @return
	  */
	 public List<Map<String,Object>> getAppUtmSource(int platform) {
		 
		 String sql =  "select t.utm_source, "
				 +"       sum(t.client_number) as client_number, "
				 +"       nvl(round(sum(t.client_number) / max(t.total_client_number), 4), 0) as client_number_rate "
				 +"from ( "
				 +"               select date_day, "
				 +"               utm_source, "
				 +"               client_number, "
				 +"               sum(client_number) OVER(PARTITION BY date_day) AS total_client_number "
				 +"               from DV_LIVE_APP_SUMMARIZE "
				 +"               where site_id = "+platform+"  "
				 +"               and date_day = trunc(sysdate) "
				 +"               and utm_source is not null "
				 +"  ) t "
				 +" group by t.utm_source "
				 +" order by t.utm_source ";
		 
		 NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(mJdbcTemplate);
		 MapSqlParameterSource parameters = new MapSqlParameterSource();
		 List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql, parameters);
		 return rows;
		 
	 }
	 
	 /**
	  * 活跃 用户数
	  * @param utmSource
	  * @return
	  */
	 public List<Map<String,Object>> getAppUtmSourceClientNumber(int platform,String utmSource,String today, String contrastday) {
		 
		 StringBuffer  sql =  new StringBuffer("select to_char(t.date_day, 'YYYY-MM-DD') as dataDate, "
				 +"      t.t_hour, "
				 +"      sum(t.client_number) as client_number "
				 +"from DV_LIVE_APP_SUMMARIZE t "
				 +"where site_id = "+platform+"  "
				 +"and (date_day = date '"+today+"' or date_day = date '"+contrastday+"') ");
		 
		 if(StringUtils.isNotEmpty(utmSource)){
			 sql.append("and utm_source = '"+utmSource+"' ");
		 }
		 sql.append("group by t.date_day, t.t_hour order by t.date_day, t.t_hour");
		 
		 NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(mJdbcTemplate);
		 MapSqlParameterSource parameters = new MapSqlParameterSource();
		 List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql.toString(), parameters);
		 return rows;
	 }
	 /**
	  * 活跃 打开次数
	  * @param utmSource
	  * @return
	  */
	 public List<Map<String,Object>> getAppUtmSourceOpenNumber(int platform,String utmSource,String today, String contrastday) {
		 
		 StringBuffer  sql =  new StringBuffer("select to_char(t.date_day, 'YYYY-MM-DD') as dataDate, "
				 +"      t.t_hour, "
				 +"      sum(t.open_number) as open_number "
				 +"from DV_LIVE_APP_SUMMARIZE t "
				 +"where site_id = "+platform+"  "
				 +"and (date_day = date '"+today+"' or date_day = date '"+contrastday+"') ");
		 
		 if(StringUtils.isNotEmpty(utmSource)){
			 sql.append("and utm_source = '"+utmSource+"' ");
		 }
		 sql.append("group by t.date_day, t.t_hour order by t.date_day, t.t_hour");
		 
		 NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(mJdbcTemplate);
		 MapSqlParameterSource parameters = new MapSqlParameterSource();
		 List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql.toString(), parameters);
		 return rows;
	 }
	 
	//************************************************地域******************************************************

	 
	 /**
	  * 地域
	  * @return
	  */
	 public List<Map<String,Object>> getAppAreaDate(int platform) {
		 
		 String sql =   "select t.province, "
				 +"       sum(t.client_number) as client_number, "
				 +"       sum(t.open_number) as open_number, "
				 +"       nvl(round(sum(t.client_number) / max(t.total_client_number), 4), 0) as client_number_rate, "
				 +"       nvl(round(sum(t.open_number) / max(t.total_open_number), 4), 0) as open_number_rate "
				 +"from ( "
				 +"               select date_day, "
				 +"               province, "
				 +"               client_number, "
				 +"               open_number, "
				 +"               sum(client_number) OVER(PARTITION BY date_day) AS total_client_number, "
				 +"               sum(open_number) OVER(PARTITION BY date_day) AS total_open_number "
				 +"               from DV_LIVE_APP_SUMMARIZE "
				 +"               where site_id = "+platform+"  "
				 +"               and date_day = trunc(sysdate) "
				 +"               and versionname is not null "
				 +"  ) t "
				 +" group by t.province "
				 +" order by client_number " ;
		 
		 NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(mJdbcTemplate);
		 MapSqlParameterSource parameters = new MapSqlParameterSource();
		 List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql, parameters);
		 return rows;
	 }
}















